package com.yj.web.dao.mapper;

import com.yj.web.dao.po.admin.Employee;
import com.yj.web.dao.po.admin.Introducer;
import com.yj.web.dao.po.admin.UserProjectByIntroducer;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Set;

/**
 * Created by yanlinyang on 2017/7/24.
 */
@Component
public interface IntroducerMapper {
    //管理员
    @Select("select * from introducer where deleted_flag = 'N' order by created_at desc limit #{size} offset #{offset}")
    public List<Introducer> listIntroducerByAdmin(@Param("offset") int offset, @Param("size") int size);
    @Select("select count(*) from introducer where deleted_flag = 'N'")
    public int countIntroducerByAdmin();

    //业务员
    @Select("select * from introducer where deleted_flag = 'N' and link_employee = #{employeeId} order by created_at desc limit #{size} offset #{offset}")
    public List<Introducer> listIntroducerByEmployee(@Param("employeeId") long employeeId, @Param("offset") int offset, @Param("size") int size);
    @Select("select count(*) from introducer where deleted_flag = 'N' and link_employee = #{employeeId}")
    public int countIntroducerByEmployee(@Param("employeeId") long employeeId);

    //业务经理
    @Select("<script>" +
            "select * from introducer where deleted_flag = 'N' and link_employee in " +
            "<foreach item=\"id\" index=\"index\" collection=\"ids\" \n" +
            "       open=\"(\" separator=\",\" close=\")\">\n" +
            "       #{id}" +
            "</foreach> " +
            " order by created_at desc limit #{size} offset #{offset}" +
            "</script>")
    public List<Introducer> listIntroducerByManager(@Param("ids") List<Long> ids, @Param("offset") int offset, @Param("size") int size);

    @Select("<script>" +
            "select count(*) from introducer where deleted_flag = 'N' and id in " +
            "<foreach item=\"id\" index=\"index\" collection=\"ids\" \n" +
            "       open=\"(\" separator=\",\" close=\")\">\n" +
            "       #{id}" +
            "</foreach> " +
            "</script>")
    public int countIntroducerByManager(@Param("ids") List<Long> ids);
//    @Select("select * from introducer where deleted_flag = 'N' and link_employee in" +
//            "<foreach item=\"employee\" index=\"index\" collection=\"employees\" \n" +
//            "       open=\"(\" separator=\",\" close=\")\">\n" +
//            "       #{employee.id}" +
//            "</foreach> " +
//            " order by id asc limit #{size} offset #{offset}")
//    public List<Introducer> listIntroducerByManager(@Param("employees") List<Employee> employees, @Param("offset") int offset, @Param("size") int size);
//    @Select("select count(*) from introducer where deleted_flag = 'N' and link_employee in" +
//            "<foreach item=\"employee\" index=\"index\" collection=\"employees\" \n" +
//            "       open=\"(\" separator=\",\" close=\")\">\n" +
//            "       #{employee.id}" +
//            "</foreach> ")
//    public int countIntroducerByManager(@Param("employees") List<Employee> employees);

    @Delete("update introducer set deleted_flag = 'Y' where id = #{id} and deleted_flag = 'N'")
    public int deleteIntroducerById(@Param("id") long id);


    @Insert("insert into introducer (create_id, created_at, type, name, phone, status, organization, QRcode, link_employee) " +
            "values (#{introducer.create_id}, now(), #{introducer.type}, #{introducer.name}, #{introducer.phone}, #{introducer.status}," +
            "#{introducer.organization}, #{introducer.QRcode}, #{introducer.link_employee})")
    @Options(useGeneratedKeys = true, keyProperty = "introducer.id")
    public int insertIntroducer(@Param("introducer") Introducer introducer);


    @Update("update introducer set update_id=#{introducer.update_id}, updated_at=now(), type=#{introducer.type}, name=#{introducer.name}, " +
            "phone=#{introducer.phone}, status=#{introducer.status}, organization=#{introducer.organization}, QRcode=#{introducer.QRcode}, " +
            "link_employee=#{introducer.link_employee} where id = #{introducer.id} and deleted_flag = 'N'")
    public boolean updateIntroducer(@Param("introducer") Introducer introducer);
    @Select("select * from introducer where id = #{id} and deleted_flag = 'N'")
    public Introducer findIntroducerById(@Param("id") long id);

    /*select distinct cu.user_no, cu.nick_name, cu.user_name as phone, cu.created_at, cfp.amount/100 as amount, cfp.cur_amount/100 as cur_amount, cfp.title as projectName, cfp.status as projectStatus, introducer.name as introducerName
    from introducer left join cus_user as cu on cu.be_invite_code = introducer.phone  left join cus_fundraising_project as cfp on cu.id = cfp.create_id
    where introducer.link_employee in (6,7,8,9,17,18,37) and cu.deleted_flag = 'N'
    order by cu.created_at desc
    */
    @Select("<script>" +
            "select distinct cu.user_no, cu.nick_name, cu.user_name as phone, cu.created_at, cfp.amount/100 as amount, cfp.cur_amount/100 as cur_amount, cfp.title as projectName, cfp.status as projectStatus, introducer.name as introducerName, introducer.phone as introducerPhone " +
            "from introducer left join cus_user as cu on cu.be_invite_code = introducer.phone left join cus_fundraising_project as cfp on cu.id = cfp.create_id " +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test = \"nick_name != ''\">" +
            "instr(cu.nick_name, #{nick_name}) " +
            "</if>" +
            "<if test = \"introducer_name != ''\">" +
            "and instr(introducer.name, #{introducer_name}) " +
            "</if>" +
            "<if test = \"phone != ''\">" +
            "and instr(cu.user_name, #{phone}) " +
            "</if>" +
            "<if test = \"employees != null\">" +
            "and introducer.link_employee in " +
            "<foreach item=\"employee\" index=\"index\" collection=\"employees\" \n" +
            "       open=\"(\" separator=\",\" close=\")\">\n" +
            "       #{employee.id}" +
            "</foreach> " +
            "</if>" +
            "<if test = \"offset != -1\">" +
            "and cu.deleted_flag = 'N' " +
            "</if>" +
            "</trim>" +
            "order by cu.created_at desc limit #{size} offset #{offset}" +
            "</script>")
    public List<UserProjectByIntroducer> listUserProjectByIntroducer(@Param("nick_name") String nick_name, @Param("introducer_name") String introducer_name,
                                                              @Param("phone") String phone, @Param("employees") List<Employee> employees, @Param("offset") int offset, @Param("size") int size);

    /*select count(*)
    from introducer left join cus_user as cu on cu.be_invite_code = introducer.phone  left join cus_fundraising_project as cfp on cu.id = cfp.create_id
    where introducer.link_employee in (6,7,8,9,17,18,37) and cu.deleted_flag = 'N'
    order by introducer.name asc,cu.user_no asc*/
    @Select("<script>" +
            "select count(*) from introducer left join cus_user as cu on cu.be_invite_code = introducer.phone left join cus_fundraising_project as cfp on cu.id = cfp.create_id " +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test = \"nick_name != ''\">" +
            "instr(cu.nick_name, #{nick_name}) " +
            "</if>" +
            "<if test = \"introducer_name != ''\">" +
            "and instr(introducer.name, #{introducer_name}) " +
            "</if>" +
            "<if test = \"phone != ''\">" +
            "and instr(cu.user_name, #{phone}) " +
            "</if>" +
            "<if test = \"employees != null\">" +
            "and introducer.link_employee in " +
            "<foreach item=\"employee\" index=\"index\" collection=\"employees\" \n" +
            "       open=\"(\" separator=\",\" close=\")\">\n" +
            "       #{employee.id}" +
            "</foreach> " +
            "</if>" +
            "<if test = \"phone != '-1'\">" +
            "and cu.deleted_flag = 'N' " +
            "</if>" +
            "</trim>" +
            "</script>")
    public int countUserProjectByIntroducer(@Param("nick_name") String nick_name, @Param("introducer_name") String introducer_name,
                                            @Param("phone") String phone, @Param("employees") List<Employee> employees);

    @Update("update introducer set status=#{status} where id = #{id}")
    public boolean updateIntroducerStatus(@Param("id") int id, @Param("status") int status);

    @Update("update introducer set QRcode=#{QRcode} where id = #{id}")
    public boolean updateQrcode(@Param("id") int id, @Param("QRcode") String QRcode);

    //渠道粉丝
    @Insert("insert into fans (openId, intro_phone) values (#{openId}, #{intro_phone})")
    @Options(useGeneratedKeys = true, keyProperty = "fans.id")
    public int insertFans(@Param("openId") String openId, @Param("intro_phone") String intro_phone);

    @Select("select openId from fans")
    public Set<String> getAllOpenIds();

    @Update("update fans set intro_phone=#{intro_phone} where openId = #{openId}")
    public boolean updateFans(@Param("openId") String openId, @Param("intro_phone") String intro_phone);

    @Select("select intro_phone from fans where openId = #{openId}")
    public String getIntroducerPhoneByOpenId(@Param("openId") String openId);
}
